package com.gsm.projects.jobDw.mock;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Iterator;

public class ExcelToMySQL {

    public static void main(String[] args) {

        String excelFilePath = "college.xls";  // 修改为你的Excel文件路径
        String jdbcUrl = "jdbc:mysql://localhost:3306/db_job_realtime_statistics?useSSL=false&serverTimezone=UTC";
        String username = "root";
        String password = "123456";

        try (
             FileInputStream fis = new FileInputStream(new File(excelFilePath));
             Workbook workbook = getWorkbook(fis, excelFilePath);
             Connection connection = DriverManager.getConnection(jdbcUrl, username, password)) {

            Sheet sheet = workbook.getSheetAt(0);  // 获取第一个工作表
            Iterator<Row> rowIterator = sheet.iterator();

//            // 跳过标题行
//            if (rowIterator.hasNext()) {
//                rowIterator.next();
//            }

            // 准备SQL语句
            String sql = "INSERT INTO Colleges (name, school_code, department, location, education_level, remarks) VALUES (?, ?, ?, ?, ?, ?)";
            PreparedStatement statement = connection.prepareStatement(sql);

            while (rowIterator.hasNext()) {

                Row row = rowIterator.next();

                if (row.getCell(1) == null) {
                    break;  // 如果遇到空行，停止读取
                }

                // 读取单元格数据
                String name = getStringCellValue(row.getCell(1));
                String schoolCode = getStringCellValue(row.getCell(2));

                System.out.println(schoolCode);

                String department = getStringCellValue(row.getCell(3));
                String location = getStringCellValue(row.getCell(4));
                String educationLevel = getStringCellValue(row.getCell(5));
                String remarks = getStringCellValue(row.getCell(6));

                // 设置参数并执行插入
                statement.setString(1, name);
                statement.setString(2, schoolCode);
                statement.setString(3, department);
                statement.setString(4, location);
                statement.setString(5, educationLevel);
                statement.setString(6, remarks);

                statement.addBatch();

//                statement.executeUpdate();
            }

            // 执行批量插入
//            statement.executeBatch();

            System.out.println("Data inserted successfully.");

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static Workbook getWorkbook(FileInputStream fis, String filePath) throws Exception {
        if (filePath.endsWith(".xlsx")) {
            return new XSSFWorkbook(fis);
        } else if (filePath.endsWith(".xls")) {
            return new HSSFWorkbook(fis);
        } else {
            throw new IllegalArgumentException("Unsupported file format: " + filePath);
        }
    }

    private static String getStringCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }

        System.out.println(cell.getCellType());

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
//                    System.out.println(cell.getNumericCellValue());
//                    System.out.println(String.valueOf((int) cell.getNumericCellValue()));
//                    4.111010002E9 -> 2147483647 因为超出int表示范围了
//                    return String.valueOf((int) cell.getNumericCellValue());

                    long value = (long) cell.getNumericCellValue();
                    String strValue = String.valueOf(value);

                    return strValue;
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            default:
                return "";
        }
    }
}